﻿using Infragistics.Documents.Excel;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Security.Policy;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using System.Windows.Forms;
using System.Xml.Linq;

namespace biaoqian
{
    public partial class Form3 : Form
    {
        LDhuoyunEntities db = new LDhuoyunEntities();
        Class1 one = new Class1();
        public Form3()
        {
            InitializeComponent();
        }

        private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void Form3_Load(object sender, EventArgs e)
        {
            cxsj();
            GET_CYDW();
            GET_CLXX();
            GET_CXXX();
            label4.Text = comboBox1.SelectedValue.ToString();
        }
        private void GET_CYDW()
        {
            var item = db.CYDW.ToList();
            DataTable dataTable = one.CYDW();
            foreach (CYDW cydw in item)
            {
                dataTable.Rows.Add(cydw.cy_id, cydw.cy_name);
            }
            
            comboBox1.DisplayMember = "name";
            comboBox1.ValueMember = "id";
            comboBox1.DataSource = dataTable;
        }
        private void GET_CXXX()
        {
            DataTable dataTable = one.CXXX();
            int id = int.Parse(comboBox1.SelectedValue.ToString());
            var cxxx = db.CXSJ.Where(x => x.cx_ss == id).ToList();
            var list = from a in cxxx
                       select new  {a.cx_name,cx_ss=a.CYDW.cy_name,a.cx_sh,cx_ss2=a.cx_ss};

            foreach(CXSJ cx in cxxx)
            {
                dataTable.Rows.Add(cx.cx_name,cx.CYDW.cy_name,cx.cx_sh,cx.cx_id);
            }
            dataGridView1.DataSource = dataTable;
        }


        private void GET_CLXX(int cx_id=0)
        {
            int id = int.Parse(comboBox1.SelectedValue.ToString());
            var clxx = db.CLSJ.Where(x => x.cl_ss == id).ToList();
            if (cx_id != 0)
            {
                clxx = db.CLSJ.Where(x => x.cl_cx == cx_id).ToList();
            }
            dataGridView2.DataSource = clxx;
        }

        private void button1_Click(object sender, EventArgs e)
        {

        }
        private void cxsj()
        {
            DataGridViewImageColumn deleteColumn = new DataGridViewImageColumn();
            deleteColumn.HeaderText = "查看详细";
            deleteColumn.Name = "View_details";
            //deleteColumn.ImageLayout =  DataGridViewImageCellLayout.Stretch;
            dataGridView1.Columns.Add(deleteColumn);

            DataGridViewTextBoxColumn cx_name = new DataGridViewTextBoxColumn();
            cx_name.HeaderText = "车型名称";
            cx_name.DataPropertyName = "cx_name";
            dataGridView1.Columns.Add(cx_name);


            DataGridViewTextBoxColumn cx_ss = new DataGridViewTextBoxColumn();
            cx_ss.HeaderText = "承运单位";
            cx_ss.DataPropertyName = "cx_ss";
            dataGridView1.Columns.Add(cx_ss);

            DataGridViewTextBoxColumn cx_sh = new DataGridViewTextBoxColumn();
            cx_sh.HeaderText = "审核状态";
            cx_sh.DataPropertyName = "cx_sh";
            dataGridView1.Columns.Add(cx_sh);

            DataGridViewTextBoxColumn cx_id = new DataGridViewTextBoxColumn();
            cx_id.Visible = false;
            cx_id.HeaderText = "车型id";
            cx_id.DataPropertyName = "cx_id";
            Name = "cx_id";
            dataGridView1.Columns.Add(cx_id);




            DataGridViewTextBoxColumn jw_bian = new DataGridViewTextBoxColumn();
            jw_bian.HeaderText = "阶梯";
            jw_bian.DataPropertyName = "jw_bian";
            dataGridView3.Columns.Add(jw_bian);


            DataGridViewTextBoxColumn jw_jt = new DataGridViewTextBoxColumn();
            jw_jt.HeaderText = "阶梯位";
            jw_jt.DataPropertyName = "jw_jt";
            dataGridView3.Columns.Add(jw_jt);

            DataGridViewTextBoxColumn jw_jg = new DataGridViewTextBoxColumn();
            jw_jg.HeaderText = "价格";
            jw_jg.DataPropertyName = "jw_jg";
            dataGridView3.Columns.Add(jw_jg);


            //车辆表头
            DataGridViewTextBoxColumn cl_id = new DataGridViewTextBoxColumn();
            cl_id.HeaderText = "车辆id";
            cl_id.DataPropertyName = "cl_id";
            dataGridView2.Columns.Add(cl_id);


            DataGridViewTextBoxColumn cl_cp = new DataGridViewTextBoxColumn();
            cl_cp.HeaderText = "车牌号";
            cl_cp.DataPropertyName = "cl_cp";
            dataGridView2.Columns.Add(cl_cp);


            DataGridViewTextBoxColumn cl_cc = new DataGridViewTextBoxColumn();
            cl_cc.HeaderText = "车长";
            cl_cc.DataPropertyName = "cl_cc";
            dataGridView2.Columns.Add(cl_cc);


            DataGridViewTextBoxColumn cl_ck = new DataGridViewTextBoxColumn();
            cl_ck.HeaderText = "车宽";
            cl_ck.DataPropertyName = "cl_ck";
            dataGridView2.Columns.Add(cl_ck);

            DataGridViewTextBoxColumn cl_dw = new DataGridViewTextBoxColumn();
            cl_dw.HeaderText = "吨位";
            cl_dw.DataPropertyName = "cl_dw";
            dataGridView2.Columns.Add(cl_dw);

            DataGridViewTextBoxColumn cl_zz = new DataGridViewTextBoxColumn();
            cl_zz.HeaderText = "载重";
            cl_zz.DataPropertyName = "cl_zz";
            dataGridView2.Columns.Add(cl_zz);

            DataGridViewTextBoxColumn cl_rj = new DataGridViewTextBoxColumn();
            cl_rj.HeaderText = "容积";
            cl_rj.DataPropertyName = "cl_rj";
            dataGridView2.Columns.Add(cl_rj);

            DataGridViewTextBoxColumn cl_ss = new DataGridViewTextBoxColumn();
            cl_ss.Visible = false;
            cl_ss.HeaderText = "发货体积";
            cl_ss.DataPropertyName = "cl_ss";
            dataGridView2.Columns.Add(cl_ss);

            DataGridViewTextBoxColumn cl_cx = new DataGridViewTextBoxColumn();
            cl_cx.Visible = false;
            cl_cx.HeaderText = "发货体积";
            cl_cx.DataPropertyName = "cl_cx";
            dataGridView2.Columns.Add(cl_cx);

            DataGridViewTextBoxColumn CXSJ = new DataGridViewTextBoxColumn();
            CXSJ.Visible = false;
            CXSJ.HeaderText = "发货体积";
            CXSJ.DataPropertyName = "CXSJ";
            dataGridView2.Columns.Add(CXSJ);

            DataGridViewTextBoxColumn CYDW = new DataGridViewTextBoxColumn();
            CYDW.Visible = false;
            CYDW.HeaderText = "发货体积";
            CYDW.DataPropertyName = "CYDW";
            dataGridView2.Columns.Add(CYDW);
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            
            GET_CXXX();
            GET_CLXX();
            label4.Text= comboBox1.SelectedValue.ToString();
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (dataGridView1.Columns[e.ColumnIndex].Name == "View_details")
            {
                int thirdColumnCellValue = int.Parse(dataGridView1.Rows[e.RowIndex].Cells[4].Value.ToString());
                label3.Text = thirdColumnCellValue.ToString();
                GET_CLXX(thirdColumnCellValue);
                GET_jtj(thirdColumnCellValue);
            }
        }

        private void GET_jtj(int cx_id=0)
        {
            if (cx_id != 0)
            {
                var list = db.YFJG.Where(x => x.jw_cx == cx_id).ToList();
                DataTable dataTable = one.JTJG();
                foreach(YFJG jt in list)
                {
                    dataTable.Rows.Add(jt.jw_bian, jt.jw_jt, jt.jw_jg);
                }
                dataGridView3.DataSource = dataTable;
            }
            
        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void button3_Click(object sender, EventArgs e)
        {
            ADDCLXX add = new ADDCLXX();
            add.Show();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            ADDCYDW cydw = new ADDCYDW();
            cydw.Show();
        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            //string jt = textBox1.Text;
            //string jg = textBox2.Text;
            //string bian = "阶梯";
            //if (jt == null || jg == null)
            //{
            //    MessageBox.Show("阶梯价为空");
            //}
            //else
            //{
            //    DataTable dataTable = one.JTJG();
            //    foreach (DataGridViewRow item in dataGridView3.Rows)
            //    {
            //        dataTable.Rows.Add(item.Cells[0].Value.ToString(), item.Cells[1].Value.ToString(), item.Cells[2].Value.ToString());
            //    }
            //    dataTable.Rows.Add(bian, jt, jg);

            //    DataView dataView = dataTable.DefaultView;
            //    // 假设我们想按年龄升序排序  
            //    DataTable dataTable2 = one.JTJG();
            //    dataView.Sort = "jw_jt ASC";
            //    foreach (DataRowView rowView in dataView)
            //    {
            //        dataTable2.Rows.Add(rowView[0], rowView[1], rowView[2]);
            //    }
            //    dataGridView3.DataSource = dataTable2;
            //}

            //OpenFileDialog openFileDialog = new OpenFileDialog();
            //if (openFileDialog.ShowDialog() == DialogResult.OK)
            //{
            //    // 获取用户选择的文件路径  
            //    string filePath = openFileDialog.FileName;
            //    // 在这里，你可以使用 filePath 来做你想做的事情，比如读取文件内容或更新数据库连接字符串  
            //    MessageBox.Show("你选择了文件: " + filePath);
            //}

            string physicalPath1 = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file\\阶梯价文件.xlsx");
            OpenFileWithWPS(physicalPath1);


            //// 如果文件不存在，创建一个新的Excel文件  
            //if (!File.Exists(physicalPath1))
            //{
            //    MessageBox.Show("文件不存在！");
            //    return;
            //}
            //if(label3.Text== "label3")
            //{
            //    MessageBox.Show("请先选择车型");
            //}
            //else
            //{
            //    // 使用EPPlus打开Excel文件  
            //    ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
            //    using (ExcelPackage excel = new ExcelPackage(new FileInfo(physicalPath1)))
            //    {
            //        // 获取或创建工作表（Worksheet）  
            //        //ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1"); 
            //        // 如果Sheet1不存在则创建  
            //        ExcelWorksheet worksheet = excel.Workbook.Worksheets[0];
            //        int i = 2;
            //        foreach (DataGridViewRow dataGridView in dataGridView3.Rows)
            //        {
            //            int index = 1;
            //            worksheet.Cells[i, index].Value = dataGridView.Cells[0].Value.ToString();
            //            index++;
            //            worksheet.Cells[i, index].Value = dataGridView.Cells[1].Value.ToString();
            //            index++;
            //            worksheet.Cells[i, index].Value = dataGridView.Cells[2].Value.ToString();
            //            index++;
            //            worksheet.Cells[i, index].Value = label3.Text;
            //            i++;
            //        }
            //        // 保存更改  
            //        excel.SaveAs(physicalPath1);
            //    }

            //    Process.Start("EXCEL.EXE", physicalPath1);
            //}

        }

        private void button4_Click(object sender, EventArgs e)
        {
            ADDCXSJ cxsj = new ADDCXSJ();
            cxsj.Show();
        }

        private void button5_Click(object sender, EventArgs e)
        {
            string physicalPath1 = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "file\\阶梯价文件.xlsx");
            ExcelFilePath(physicalPath1);
            

        }


        public static System.Data.DataTable ExcelToDatatable(string fileName, string sheetName, bool isFirstRowColumn)
        {
            System.Data.DataTable data = new System.Data.DataTable();
            int startRow = 0;
            int cellCount = 0;//列数
            int rowCount = 0;//行数
            try
            {
                using (var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    // 创建HSSFWorkbook或XSSFWorkbook对象，取决于你的Excel文件格式  
                    IWorkbook workbook;
                    if (System.IO.Path.GetExtension(fileName).Equals(".xls"))
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    else
                    {
                        workbook = new XSSFWorkbook(fs);
                    }

                    // 获取第一个工作表  
                    ISheet sheet = workbook.GetSheetAt(0);
                    if (sheet != null)
                    {
                        IRow firstRow = sheet.GetRow(0);
                        cellCount = firstRow.LastCellNum; //第一行最后一个cell的编号 即总的列数
                        if (isFirstRowColumn)//如果第一行是标题行
                        {
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)//第一行列数循环
                            {
                                DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);//获取标题
                                data.Columns.Add(column);//添加列
                            }
                            startRow = sheet.FirstRowNum + 1;//1（即第二行，第一行0从开始）
                        }
                        else
                        {
                            startRow = sheet.FirstRowNum;//0
                        }
                        //最后一行的标号
                        rowCount = sheet.LastRowNum;
                        for (int i = startRow; i <= rowCount; ++i)//循环遍历所有行
                        {
                            IRow row = sheet.GetRow(i);//第几行
                            if (row != null)
                            {
                                //将excel表每一行的数据添加到datatable的行中
                                DataRow dataRow = data.NewRow();
                                for (int j = row.FirstCellNum; j < cellCount; ++j)
                                {
                                    if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                    {
                                        dataRow[j] = row.GetCell(j).ToString();
                                    }
                                }
                                data.Rows.Add(dataRow);
                            }
                            else
                            {
                                continue; //没有数据的行默认是null;
                            }

                        }
                    }
                    return data;
                }
            }
            catch (Exception)
            {

                return data;
            }
        }
        private void OpenFileWithWPS(string filePath)
        {
            try
            {
                // 假设WPS文字的安装路径为C:\Program Files (x86)\Kingsoft\WPS Office\11.2.0.10316\office6\wps.exe  
                // 注意：根据你的WPS Office版本和安装路径，这里的路径可能有所不同  
                string wpsPath = @"C:\Users\Administrator\AppData\Local\kingsoft\WPS Office\ksolaunch.exe";

                // 创建一个新的ProcessStartInfo实例  
                ProcessStartInfo startInfo = new ProcessStartInfo(wpsPath, $"\"{filePath}\"");
                startInfo.UseShellExecute = true; // 设置为true，以便操作系统处理文件关联  

                // 启动进程  
                Process.Start(startInfo);
            }
            catch (Exception ex)
            {
                // 处理错误，例如WPS未安装或文件路径无效  
                MessageBox.Show($"Error opening file with WPS: {ex.Message}");
            }
        }

        public void ExcelFilePath(string physicalPath1)
        {
            System.Data.DataTable table = ExcelToDatatable(physicalPath1, "Sheet1", true);


            //自定义一个datatable
            var table1 = new System.Data.DataTable();
            table1.Columns.Add("jw_bian", typeof(string));
            table1.Columns.Add("jw_jt", typeof(string));
            table1.Columns.Add("jw_jg", typeof(string));

            int temp = 0;
            //去除空值
            foreach (DataRow row in table.Rows)
            {

                if (row[0].ToString() != "")
                {
                    table1.Rows.Add(row[0], row[1], row[2]);
                }
            }
            if(label3.Text!= "label3")
            {
                int cx = int.Parse(label3.Text);
                int ss = int.Parse(label4.Text);
                int index = 0;
                var list = db.YFJG.Where(x => x.jw_cx == cx && x.jw_gs == ss);
                //foreach(YFJG yf in list)
                //{
                //    db.YFJG.Remove(yf);
                //    db.SaveChanges();
                //}

                foreach (DataRow data in table1.Rows)
                {
                    YFSH yf = new YFSH()
                    {
                        ss_bian = data[0].ToString(),
                        ss_jt = data[1].ToString(),
                        ss_jg = data[2].ToString(),
                        ss_cx = cx,
                        ss_ss = ss,
                        ss_sh = "审核"
                    };
                    db.YFSH.Add(yf);
                    index += db.SaveChanges();
                }
                if (index != 0)
                {
                    CXSJ cx2 = db.CXSJ.Where(x => x.cx_id == cx).FirstOrDefault();
                    cx2.cx_sh = "待审核";
                    int sl = db.SHZT.Where(x => x.sh_zhuantiao == ss.ToString()).Count();
                    if (sl > 0)
                    {
                        SHZT slzt = db.SHZT.Where(x => x.sh_zhuantiao == ss.ToString()).FirstOrDefault();
                        slzt.sh_zt = "待审核";
                        db.SaveChanges();
                    }
                    else
                    {
                        SHZT sHZT = new SHZT()
                        {
                            sh_zt = "待审核",
                            sh_lx = "承运商报价审核",
                            sh_ren = "无",
                            sh_zhuantiao = ss.ToString()
                        };
                        db.SHZT.Add(sHZT);
                        db.SaveChanges();
                    }

                    MessageBox.Show("阶梯价已经提交到审核");
                }
            }
            else
            {
                MessageBox.Show("请先选择车型");
            }
            
        }

    }
}
